import random
import shutil
import requests, urllib3
import time
import os
import zipfile
from openpyxl import load_workbook, Workbook
from datetime import datetime
from bs4 import BeautifulSoup
import yaml
from openpyxl.styles import Font, Alignment
import subprocess

with open('config.yml', 'r', encoding='utf-8') as f:
    config = yaml.safe_load(f)

class UpdateHuiwuDoc:
    def __init__(self, cookie_huiwu, cookie_oa, pageNum, isAuto, mode):
        self.cookie_huiwu = cookie_huiwu
        self.cookie_oa = cookie_oa
        self.pageNum = pageNum
        self.isAuto = isAuto
        self.file_path = config['home_dir'] + '/收文本.xlsx'
        self.dept_mapping = {
                "市发展改革局": "市发改局",
                "市工业和信息化局": "市工信局",
                "市住房城乡建设局": "市住建局",
                "市机关事务管理局": "市机关事务管",
                "市人大常委会办公室": "市人大常委办",
                "市政府办公室": "市府办",
                "市应急管理局": "市应急局",
                "市市场监督管理局": "市市场监管局",
                "百千万指挥办": "市百千万指挥办",
                "市人力资源社会保障局": "市人社局",
                "市卫生健康局": "市卫健局",
                "市直机关工委": "市直工委"
            }
        self.mode = mode
    def doUpdate(self):
        jsonContent = self.getContent()
        if not jsonContent:
            return None
        # 倒序
        jsonContent = jsonContent[::-1]
        return self.writeInExcel(jsonContent)

    def writeInExcel(self, jsonContent):
        
        # 如果当前文件夹中有文件名存在‘临时文件’，则删除
        for file in os.listdir(config['code_dir']):
            if '临时文件' in file:
                # 而且后缀名为.xlsx
                if file.endswith('.xlsx'):
                    # 去到指定的目录
                    os.chdir(config['code_dir'])
                    os.remove(file)
        
        if self.isAuto:
            if os.path.exists('收文本_备份.xlsx'):
                os.remove('收文本_备份.xlsx')
                shutil.copy('收文本.xlsx', '收文本_备份.xlsx')
            # 加载一个已存在的Excel文件
            wb = load_workbook(self.file_path)
            # 检查是否存在指定的工作表
            if '会务系统' not in wb.sheetnames:
                print("工作表 '会务系统' 不存在")
                return None
            # 获取指定的活动工作表
            ws = wb['会务系统']
            # 获取第四列最后一个非空单元格的行号
            last_row = ws.max_row
            last_cell = ws.cell(row=last_row, column=4)
            last_title = last_cell.value
            # TODO 确保xlsx文件格式正确
            if not last_title:
                print("第四列最后一个非空单元格为空")
                return None
            print("last_title: " + last_title)
            # 从jsonContent中找到last_title
            for item in jsonContent:
                if item['FName'] == last_title:
                    # 截取jsonContent中last_title后面的所有数据，不包含last_title
                    jsonContent = jsonContent[jsonContent.index(item)+1:]
                    break
        else:
            # 检查是否存在会务收文本.xlsx
            if os.path.exists(config['code_dir'] + '/会务收文本.xlsx'):
                os.remove(config['code_dir'] + '/会务收文本.xlsx')
            # 新建一个Excel文件
            wb = Workbook()
            ws = wb.active
            ws.title = '会务系统'
            
        # 设置格式
        
        row_num = ws.max_row
        # 设置各列宽度（单位：字符宽度）
        ws.column_dimensions['B'].width = 10.0  # 第2列
        ws.column_dimensions['C'].width = 15.0  # 第3列
        ws.column_dimensions['D'].width = 70.0  # 第4列
        ws.column_dimensions['G'].width = 15.0  # 第7列
        ws.column_dimensions['H'].width = 70.0  # 第8列
        
        # === 定义对齐方式（垂直居中 + 自动换行） ===
        alignment = Alignment(
            vertical='center',    # 垂直居中
            horizontal='left',    # 水平左对齐（可选：'center', 'right'）
            wrapText=True         # 自动换行
        )
        
        # 定义字体样式（12号方正小标宋简体）
        header_font = Font(
            name='方正小标宋简体',  # 确保系统已安装该字体
            size=12,
            bold=False  # 不加粗
        )
        if row_num == 1:
            ws.cell(row=row_num, column=1, value='收文号').alignment = alignment
            ws.cell(row=row_num, column=2, value='日期').alignment = alignment
            ws.cell(row=row_num, column=3, value='来文单位').alignment = alignment
            ws.cell(row=row_num, column=4, value='标题').alignment = alignment
            ws.cell(row=row_num, column=5, value='收文处理').alignment = alignment
            ws.cell(row=row_num, column=6, value='限办日期').alignment = alignment
            ws.cell(row=row_num, column=7, value='责任科室/人员').alignment = alignment
            ws.cell(row=row_num, column=8, value='直达链接（需要先登录OA）').alignment = alignment
            # 应用字体样式到表头
            for col in range(1, 9):
                ws.cell(row=row_num, column=col).font = header_font
            # 设置行高为25
            ws.row_dimensions[row_num].height = 25
            # 冻结窗格（冻结到表头行下方，即B2单元格上方）
            ws.freeze_panes = 'A2'

        # 2. 写入数据
        for data in jsonContent:
            oaData = self.searchInOa(data['FName'])
            dep = oaData.get('dep', '') if oaData else ''
            url = oaData.get('url', '') if oaData else ''
            row_num += 1
            ws.cell(row=row_num, column=2, value=data['FSendTime']).alignment = alignment
            ws.cell(row=row_num, column=3, value=data['FCreateDept']).alignment = alignment
            ws.cell(row=row_num, column=4, value=data['FName']).alignment = alignment
            ws.cell(row=row_num, column=7, value=dep).alignment = alignment
            link_cell = ws.cell(row=row_num, column=8, value=url)
            link_cell.hyperlink = url  # 设置超链接
            link_cell.font = Font(color="0000FF", underline="single")  # 蓝色 + 单下划线
            link_cell.alignment = alignment  # 应用垂直居中 + 自动换行
        
        # 写入更新时间
        # 检查是否存在指定的工作表
        if '运行日志' in wb.sheetnames:
            # 获取指定的活动工作表
            ws = wb['运行日志']
            # 在第二列第一行写入当前时间
            ws.cell(row=2, column=2, value=datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            
        # 保存Excel文件
        if self.isAuto:
            wb.save(self.file_path)
        else:
            random_num = random.randint(1000, 9999)
            wb.save(config['code_dir'] + f"/会务收文本（临时文件{random_num}）.xlsx")
            try:
                subprocess.run(["et", config['code_dir'] + f"/会务收文本（临时文件{random_num}）.xlsx"], check=False)
            except FileNotFoundError:
                print("错误：WPS 未安装或不在 PATH 环境变量中")
            except subprocess.CalledProcessError as e:
                print(f"错误：WPS 打开失败（返回码 {e.returncode}）")
        wb.close()
        return True
    

    def getContent(self):
        # 1：0-14 
        # 2：15-29
        # 3：30-44
        # n：(n-1)*15
        if '-' in self.pageNum:
            parts = self.pageNum.split('-')  # 按 '-' 分割字符串
            num1 = int(parts[0])  # 第一个部分 '1' 转为整数
            num2 = int(parts[1])  # 第二个部分 '8' 转为整数
        else:
            num1 = int(self.pageNum)
            num2 = num1
        if num1 >= num2 :
            start = str((num1-1)*15)
            url = config['huiwu_url'] + "/getJson.html?&draw=1&start=" + start + "&length=15&page="+ str(num1) +"&limit=15&action=all&_="
            if self.mode == 'guidang':
                url = config['huiwu_url']  + "/get2Json.html?&draw=1&start=" + start + "&length=15&page="+ str(num1) +"&limit=15&action=all&_="
        else:
            start = str((num1-1)*15)
            length = str((num2-num1+1)*15)
            url = config['huiwu_url'] + "/getJson.html?&draw=1&start=" + start + "&length=" + length + "&page="+ str(num1) +"&limit=" + length + "&action=all&_="
            if self.mode == 'guidang':
                url = config['huiwu_url']  + "/get2Json.html?&draw=1&start=" + start + "&length=" + length + "&page="+ str(num1) +"&limit=" + length + "&action=all&_="
            
        # 发送GET请求（如果是POST请求，使用requests.post()）
        # print(url)
        config['huiwu_headers']['Cookie'] = self.cookie_huiwu
        response = requests.get(url + str(int(time.time() * 1000)), headers=config['huiwu_headers'], verify=False)
        # 检查请求是否成功（状态码200）
        # response.raise_for_status()
        # 解析JSON响应
        # print(response.text)
        if response.text == '请先登录系统 !':
            input('获取失败，请尝试更新会务系统的Cookie')
            return None
        
        json_data = response.json()
        # 打印或处理JSON数据
        # print("获取到的JSON数据:")
        # print(json_data)
        # 提取所需字段并去掉空格
        extracted_data = []
        for item in json_data['data']:
            # 解析日期
            date_obj = datetime.strptime(item['FSendTime'], "%Y-%m-%d %H:%M:%S")
            # 格式化为新格式
            sendTime = date_obj.strftime("%Y/%-m/%-d")
            extracted_item = {
                'rKey': item.get('rKey'),
                'FSendTime': sendTime,
                'FCreateDept': item.get('FCreateDept').replace(" ", ""),
                'FName': item.get('FName')
            }
            extracted_data.append(extracted_item)
        # 打印提取的数据
        # print(extracted_data)
        return extracted_data

    def searchInOa(self, filename):
        
        print(filename)
        
        # start 获取文件的value
        config['oa_headers']['Cookie'] = self.cookie_oa
        response = requests.get(config['oa_url']['search_url'] + filename, headers=config['oa_headers'])
        content = response.text
        if '你还没有登录，请先登录' in content:
            print('获取失败，请尝试更新oa的Cookie')
            return None
        # 从html找出value
        soup = BeautifulSoup(content, 'html.parser')
        matches = []
        for input_tag in soup.find_all('input', {'name': 'ids[]'}):
            text = input_tag.get('value')
            matches.append(text)
        # end 获取文件的value

        if matches == []:
            # print("未找到文件")
            return None
        # start 获取详情
        oaData = {'dep': '', 'url': ''}
        get_det_url = config['oa_url']['detail_url'] + matches[0]
        oaData['url'] = get_det_url
        response = requests.get(get_det_url, headers=config['oa_headers'])
        soup = BeautifulSoup(response.text, 'html.parser')
        text = soup.find('div', {'id': 'form'}).text
        # 提取字符串
        departments = ['请办公室','局办公室', '高新科', '产学研科', '法规科', '社农科', '专家科','成果科','情报所', '机关党委', '地震监测中心',
                        '送各业务科室','拟请各科室','拟请各业务科室','局工会','局务会','局妇委会','局办公室（财务）','局办公室（人事）']
        dep = [d for d in departments if d in text] # ***
        dep_to_string = '、'.join(dep).replace("拟请", "").replace("送", "").replace("局办公室、局办公室", "局办公室").replace("请办公室", "局办公室")
        # end 获取详情
        oaData['dep'] = dep_to_string or ''
        return oaData
    
# if __name__ == '__main__':
#     update_doc = UpdateHuiwuDoc('sslhw_zw=HW-2; PHPSESSID=afcffce816a1ddbba8ef048fc139f4fc', 'PHPSESSID=8regkdu7t5ov6029m8vdma4k8j; toa_auth=MjU0OAk4YzNkMjViOGRjYmE3YjllY2RiZTQ2ZDEwN2JjMTdhMg%3D%3D', 
#                                 '1', False)
#     update_doc.doUpdate()